﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DuLieu;
using System.Data.OleDb;

namespace DAO
{
    public static class PhuongDAO
    {
        public static List<PhuongDTO> GetListPhuong()
        {
            List<PhuongDTO> list = new List<PhuongDTO>();

            string query = "select MaPhuong,TenPhuong from PHUONG";
            try
            {
                DataProvider.OpenConnection();
                OleDbDataReader reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    PhuongDTO phg = new PhuongDTO();
                    if (!reader.IsDBNull(0))
                    {
                        phg.MaPhuong = reader.GetInt32(0);
                    }
                    if (!reader.IsDBNull(1))
                    {
                        phg.TenPhuong = reader.GetString(1);
                    }
                    list.Add(phg);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }

            return list;
        }

        public static PhuongDTO GetPhuongByName(string name)
        {
            string query = "select MaPhuong, TenPhuong from PHUONG where TenPhuong like " + "'" + name + "'";
            try
            {
                DataProvider.OpenConnection();
                OleDbDataReader reader = DataProvider.ExecuteReader(query);
                if (reader.Read())
                {
                    PhuongDTO dv = new PhuongDTO();
                    if (!reader.IsDBNull(0))
                        dv.MaPhuong = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        dv.TenPhuong = reader.GetString(1);
                    return dv;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
                // return null;
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return null;
        }

        public static PhuongDTO GetPhuongByID(int id)
        {
            string query = "select MaPhuong, TenPhuong from PHUONG where MaPhuong=" + id.ToString();
            try
            {
                DataProvider.OpenConnection();
                OleDbDataReader reader = DataProvider.ExecuteReader(query);
                if (reader.Read())
                {
                    PhuongDTO dv = new PhuongDTO();
                    if (!reader.IsDBNull(0))
                        dv.MaPhuong = reader.GetInt32(0);
                    if (!reader.IsDBNull(1))
                        dv.TenPhuong = reader.GetString(1);
                    return dv;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
                // return null;
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return null;
        }

        public static String GetNameByID(int id)
        {
            String result = null;
            string query = "select TenPhuong from PHUONG where MaPhuong=" + id.ToString();
            try
            {
                DataProvider.OpenConnection();
                OleDbDataReader reader = DataProvider.ExecuteReader(query);
                if (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                        result = reader.GetString(0);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
                // return null;
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return result;
        }

        public static Boolean InsertPhuong(PhuongDTO NewPhuong)
        {
            List<PhuongDTO> list = GetListPhuong();
            for (int i = 0; i < list.Count; i++)
            {
                if (list[i].MaPhuong == NewPhuong.MaPhuong)
                    return false;
            }

            string query = "insert into Phuong(MaPhuong,TenPhuong) values(" + NewPhuong.MaPhuong + "," + "'" + NewPhuong.TenPhuong + "'" + ")";

            try
            {
                DataProvider.OpenConnection();
                DataProvider.ExecuteNonQuery(query);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return true;
        }

        public static Boolean UpdatePhuong(PhuongDTO NewPhuong)
        {
            string query = "select MaPhuong, TenPhuong from PHUONG";

            try
            {
                DataProvider.OpenConnection();
                OleDbDataReader reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        if (reader.GetInt32(0) == NewPhuong.MaPhuong)
                        {
                            query = "update PHUONG set TenPhuong=" + "'" + NewPhuong.TenPhuong + "'" + " where MaPhuong=" + NewPhuong.MaPhuong;
                            DataProvider.ExecuteNonQuery(query);
                            return true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return false;
        }

        public static Boolean DeletePhuongByID(int id)
        {
            string query = "select MaPhuong, TenPhuong from PHUONG";

            try
            {
                DataProvider.OpenConnection();
                OleDbDataReader reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    if (!reader.IsDBNull(0))
                    {
                        if (reader.GetInt32(0) == id)
                        {
                            query = "delete from PHUONG where MaPhuong=" + id.ToString();
                            DataProvider.ExecuteNonQuery(query);
                            return true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return false;
        }

        public static Boolean DeletePhuongByName(string name)
        {
            string query = "select MaPhuong, TenPhuong from PHUONG";

            try
            {
                DataProvider.OpenConnection();
                OleDbDataReader reader = DataProvider.ExecuteReader(query);

                while (reader.Read())
                {
                    if (!reader.IsDBNull(1))
                    {
                        if (String.Compare(reader.GetString(1), name, true) == 0)
                        {
                            query = "delete from PHUONG where MaPhuong=" + reader.GetInt32(0).ToString();
                            DataProvider.ExecuteNonQuery(query);
                            return true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                DataProvider.CloseConnection();
            }
            return false;
        }
    }
}